﻿using System;
using System.Reflection;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Web;
using System.Net;
using Microsoft.SqlServer.Management.Smo;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.Sql;
using Microsoft.SqlServer.Management.Common;
using System.Data;
using System.IO;
using System.Data.SqlTypes;
using System.Web;
using System.Xml;

namespace Moddb08_WCF
{
    // NOTE: If you change the class name "test" here, you must also update the reference to "test" in Web.config.
    /// <summary>
    /// Sets up the Restful web service as a WCF web service
    /// </summary>
    public class test : Itest
    {
        /// <summary>
        /// The private class variables
        /// </summary>
        string connectionString;

        /// <summary>
        /// Constructor of the class. This is not needed
        /// </summary>
        public test()
        {
            connectionString = ConfigurationManager.ConnectionStrings["moddbConnectionString"].ConnectionString;



        }
        #region privateMethods

        /// <summary>
        /// Get the moddb database thru the sqlConnection variable.
        /// </summary>
        /// <param name="sqlConnection">The connection to the SQL Server</param>
        /// <returns> The Database moddb</returns>
        private Database GetDatabase(SqlConnection sqlConnection)
        {
            Server dbServer = new Server(new ServerConnection(sqlConnection));
            return dbServer.Databases["moddb"];
        }

        /// <summary>
        /// Get the server associated to the sqlConnection
        /// </summary>
        /// <param name="sqlConnection">The connection to the server</param>
        /// <returns>The Sql Server</returns>
        private Server GetDatabaseServer(SqlConnection sqlConnection)
        {
            return new Server(new ServerConnection(sqlConnection));
        }

        /// <summary>
        /// Converts an object to byte.
        /// </summary>
        /// <param name="data">The object which should be converted</param>
        /// <param name="dataType">The name of the datatype which shoule be converted</param>
        /// <returns>The byte array of the object</returns>
        private byte[] ConvertDataToByte(Object data, string dataType, string meta)
        {
            if (meta.Equals("text/plain") && !dataType.Equals("DBNull"))
            {
                data = Convert.ToString(data);
                dataType = "text";
            }
            
            
            if(data.GetType().Name.Equals("DBNull"))
            {
                return null;
            }
            Type test = data.GetType();
            string sss = test.ToString();
            if (dataType.Equals("image") || dataType.Equals("binary") || dataType.Equals("varbinary"))
            {
               
                return (byte[]) data;
            }
            else if (dataType.Equals("nchar") || dataType.Equals("varchar") || dataType.Equals("nvarchar") || dataType.Equals("text") || dataType.Equals("ntext"))
            {
                System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
                return enc.GetBytes((string)data);
            }
            else if (dataType.Equals("int"))
            {
                return BitConverter.GetBytes((int)data);
            }
            else if(dataType.Equals("decimal"))
            {
                return BitConverter.GetBytes((double) data);
            }
            else if (dataType.Equals("datetime"))
            {
                return BitConverter.GetBytes((long)data);
            }

            return null;
        }

        /// <summary>
        /// Convert the byte array back to an object.
        /// </summary>
        /// <param name="array">The byte data that should be converted</param>
        /// <param name="dataType">The datatype that the byte array should be converted to</param>
        /// <returns>Return the object of the converted data</returns>
        private Object ConvertByteToObject(byte[] array, SqlDataType dataType)
        {
            if (dataType.Equals(SqlDataType.Image) || dataType.Equals(SqlDataType.Binary) || dataType.Equals(SqlDataType.VarBinary) || dataType.Equals(SqlDataType.VarBinaryMax))
            {

                return array;
            }
            else if (dataType.Equals(SqlDataType.NChar) || dataType.Equals(SqlDataType.VarChar) || dataType.Equals(SqlDataType.NVarChar) || dataType.Equals(SqlDataType.Text) || dataType.Equals(SqlDataType.NText))
            {
                System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
                return enc.GetString(array);
            }
            else if (dataType.Equals(SqlDataType.Int))
            {
                return BitConverter.ToInt32(array,0);
            }
            else if (dataType.Equals(SqlDataType.Decimal))
            {
                return BitConverter.ToDouble(array, 0);
            }
            return null;
        }
        /// <summary>
        /// Compares a given name to the column name without the content type
        /// attached to the column name
        /// </summary>
        /// <param name="columnName">The column name of the sql column</param>
        /// <param name="name">The given name to compare with</param>
        /// <returns>True if the name is equal</returns>
        private bool CompairColumnName(string columnName, string name)
        {
            String[] test = columnName.Split(';');
            if (test[0].Equals(name))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// Get the datatype of the data
        /// </summary>
        /// <param name="dataType">The name of the datatype</param>
        /// <returns>The datatype object of the data</returns>
        private DataType ParseDataType(string dataType)
        {
            if (dataType.ToUpper().Equals("INT"))
            {
                return DataType.Int;
            }
            else if(dataType.ToUpper().Equals("SMALLINT"))
            {
                return DataType.SmallInt;
            }
            else if (dataType.ToUpper().Equals("DECIMAL"))
            {
                return DataType.Decimal(18,18);
            }
            else if (dataType.ToUpper().Equals("NCHAR"))
            {
                return DataType.NVarCharMax;
            }
            else if (dataType.ToUpper().Equals("VARCHAR"))
            {
                return DataType.VarCharMax;
            }
            else if (dataType.ToUpper().Equals("TEXT"))
            {
                return DataType.Text;
            }
            else if (dataType.ToUpper().Equals("IMAGE"))
            {
                return DataType.Image;
            }
            else if (dataType.ToUpper().Equals("MONEY"))
            {
                return DataType.Money;
            }
            else if (dataType.ToUpper().Equals("DATE"))
            {
                return DataType.Date;
            }
            else if (dataType.ToUpper().Equals("DATETIME"))
            {
                return DataType.DateTime;
            }
            else if(dataType.ToUpper().Equals("BLOB"))
            {
                return DataType.VarBinaryMax;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// Get the sql column name
        /// </summary>
        /// <param name="columnName">The given name</param>
        /// <param name="table">The table with the columns</param>
        /// <returns>The real sql column name</returns>
        private string getColumnName(string columnName, Microsoft.SqlServer.Management.Smo.Table table)
        {
            foreach (Microsoft.SqlServer.Management.Smo.Column col in table.Columns)
            {
                if (CompairColumnName(col.Name, columnName))
                {
                    return col.Name;
                }
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private byte[] GetBytesFromStream(Stream data)
        {
            List<byte> list = new List<byte>();
            BinaryReader reader = new BinaryReader(data);
            try
            {
                while (true)
                {
                    list.Add(reader.ReadByte());
                }
            }
            catch(EndOfStreamException)
            {
                byte[] array = new byte[list.Count];
                for (int i = 0; i < list.Count; i++)
                {
                    array[i] = list[i];
                }
                return array;
            }
            
        }

        private SqlDbType getSQLDbTypeFromSqlDataType(SqlDataType type)
        {
            if(type.Equals(SqlDataType.BigInt))
            {
                return SqlDbType.BigInt;
            }
            else if (type.Equals(SqlDataType.Binary))
            {
                return SqlDbType.Binary;
            }
            else if (type.Equals(SqlDataType.Bit))
            {
                return SqlDbType.Bit;
            }
            else if (type.Equals(SqlDataType.Char))
            {
                return SqlDbType.Char;
            }
            else if (type.Equals(SqlDataType.Date))
            {
                return SqlDbType.Date;
            }
            else if (type.Equals(SqlDataType.DateTime))
            {
                return SqlDbType.DateTime;
            }
            else if (type.Equals(SqlDataType.DateTime2))
            {
                return SqlDbType.DateTime2;
            }
            else if (type.Equals(SqlDataType.Int))
            {
                return SqlDbType.Int;
            }
            else if (type.Equals(SqlDataType.Image))
            {
                return SqlDbType.Image;
            }
            else if (type.Equals(SqlDataType.NChar))
            {
                return SqlDbType.NChar;
            }
            else if (type.Equals(SqlDataType.NText))
            {
                return SqlDbType.NText;
            }
            else if (type.Equals(SqlDataType.NVarChar) || type.Equals(SqlDataType.NVarCharMax))
            {
                return SqlDbType.NVarChar;
            }
            else if (type.Equals(SqlDataType.SmallInt))
            {
                return SqlDbType.SmallInt;
            }
            else if (type.Equals(SqlDataType.VarBinary) || type.Equals(SqlDataType.VarBinaryMax))
            {
                return SqlDbType.VarBinary;
            }
            else if( type.Equals(SqlDataType.VarChar) || type.Equals(SqlDataType.VarCharMax))
            {
                return SqlDbType.VarChar;
            }
            else
            {
                return SqlDbType.Text;
            }
        }

        #endregion

        /// <summary>
        /// Get all tables located in the databse
        /// </summary>
        /// <returns>The tables</returns>
        public Tables GetAllTables()
        {
            String method = WebOperationContext.Current.IncomingRequest.Method;
            
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            Database db = GetDatabase(sqlConnection);
            Tables tables = new Tables();
            for (int i = 0; i < db.Tables.Count; i++)
            {
                tables.Add(new Table(db.Tables[i].Name));
            }
            
            sqlConnection.Close();
            
            return tables;
        }
        /// <summary>
        /// Get the columns coresponding to the specified table
        /// </summary>
        /// <param name="tableName"> The name of the table</param>
        /// <returns>The columns</returns>
        public Columns GetAllColumnsOfTable(string tableName)
        {
            //TODO: Ask the database instead of the dictionary
            //but it is the same thing mostly
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            Database db = GetDatabase(sqlConnection);
            
            if (!db.Tables.Contains(tableName))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                sqlConnection.Close();
                return null;
                
            }
            else
            {
                Columns columns = new Columns();
                for (int i = 0; i < db.Tables[tableName].Columns.Count; i++)
                {
                    string[] temp = db.Tables[tableName].Columns[i].Name.Split(';');
                    columns.Add(new Column( temp[0] , db.Tables[tableName].Columns[i].DataType.Name,temp[1]));
                }

                sqlConnection.Close();
                return columns;
                
            }
        }

        /// <summary>
        /// Get all the rows id's of a certain table
        /// </summary>
        /// <param name="tableName">The name of the table</param>
        /// <returns>The rows of the table</returns>
        public Rows GetAllRows(string tableName)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            Database db = GetDatabase(sqlConnection);
            if (!db.Tables.Contains(tableName))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                sqlConnection.Close();
                return null;
            }
            else
            {
                SqlCommand command = new SqlCommand("SELECT * FROM "+ tableName,sqlConnection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);

                DataSet dataset = new DataSet();
                adapter.Fill(dataset);
                DataTable table = dataset.Tables[0];
                Rows rows = new Rows();
                foreach(DataRow row in table.Rows)
                {
                    rows.Add(new Row(int.Parse(row["id;text/plain"].ToString())));
                }
                sqlConnection.Close();
                return rows;
            }
        }

        /// <summary>
        /// Get the column data from a sertain column in a row of a table
        /// </summary>
        /// <param name="tableName">The name of the table</param>
        /// <param name="id">The id of the row</param>
        /// <param name="column">The name of the column</param>
        /// <returns>The byte stream of the data</returns>
        public Stream GetColumnData(string tableName, string id, string column)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            Database db = GetDatabase(sqlConnection);
            string columnName = null;
            int columnID = 0;

    
            if (!db.Tables.Contains(tableName))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                sqlConnection.Close();
                return null;
            }
            
            foreach(Microsoft.SqlServer.Management.Smo.Column col in db.Tables[tableName].Columns)
            {
                if(CompairColumnName(col.Name,column))
                {
                    columnName = col.Name;
                    columnID = col.ID;
                }
            }
            if (columnName == null)
            {

                
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                sqlConnection.Close();
                return null;
            }
            else
            {
                SqlCommand command = new SqlCommand("SELECT * FROM " + tableName + @" WHERE [id;text/plain] = @Id", sqlConnection);
                command.Parameters.Add("@Id", SqlDbType.Int).Value = int.Parse(id);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                SqlDataReader reader = command.ExecuteReader();
                
                MemoryStream stream = null;
                if (reader.HasRows)
                {
                    string[] temp = columnName.Split(';');
                    reader.Read();
                    string datatype = reader.GetDataTypeName(columnID - 1);
                    byte[] data = ConvertDataToByte(reader[columnID - 1], datatype, temp[1]);
                    if (data == null)
                    {
                        sqlConnection.Close();
                        return null;
                    }
                    stream = new MemoryStream(data);
                    sqlConnection.Close();
                    
                    WebOperationContext.Current.OutgoingResponse.ContentType = temp[1];
                    return stream;
                }
                else
                {
                    WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                    sqlConnection.Close();
                    return null;
                }
            }
        }

        /// <summary>
        /// Add a new table to the sql database
        /// </summary>
        /// <param name="data">The new table that is going to be crated</param>
        public void AddNewTable(Table data)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                Database db = GetDatabase(connection);
                Microsoft.SqlServer.Management.Smo.Table table = new Microsoft.SqlServer.Management.Smo.Table(db, data.Name);
                Microsoft.SqlServer.Management.Smo.Column col = new Microsoft.SqlServer.Management.Smo.Column(table, @"id;text/plain");
                col.DataType = DataType.Int;
                col.Identity = true;
                col.IdentityIncrement = 1;
                col.IdentitySeed = 1;
                Index idx = new Index(table, "PrimaryKey");

                table.Indexes.Add(idx);

                idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));

                idx.IsClustered = true;

                idx.IsUnique = true;

                idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
                col.Nullable = false;
                table.Columns.Add(col);
                db.Tables.Add(table);
                table.Create();
            }
            catch (SmoException)
            {
                //If the table exists or the database is not availible 
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.Conflict;
            }
        }      

        /// <summary>
        /// Add a column to a table
        /// </summary>
        /// <param name="input">The new column</param>
        /// <param name="tableName">The name of the table</param>
        public void AddNewColumn(Column input, string tableName)
        {
            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                Database db = GetDatabase(connection);
                if (!db.Tables.Contains(tableName))
                {
                    WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                    connection.Close();
                    return;
                }
                else
                {
                    Microsoft.SqlServer.Management.Smo.Table table = db.Tables[tableName];
                    if (getColumnName(input.Name, table) != null)
                    {
                        WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.Conflict;
                        return;
                    }
                    Microsoft.SqlServer.Management.Smo.Column col = new Microsoft.SqlServer.Management.Smo.Column(table, input.Name + ";" + input.Mime);
                    col.DataType = ParseDataType(input.Type);
                    table.Columns.Add(col);
                    col.Create();
                    connection.Close();
                    return;
                }

            }
            catch (SmoException)
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.Conflict;
            }
            
        }

        /// <summary>
        /// Delete a column
        /// </summary>
        /// <param name="tableName">The name of the table</param>
        /// <param name="columnName">The name of the column</param>
        public void DeleteColumn(string tableName, string columnName)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            Database db = GetDatabase(connection);
            if (!db.Tables.Contains(tableName))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                connection.Close();
                return;
            }
            else if (!db.Tables[tableName].Columns.Contains(columnName))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                connection.Close();
                return;
            }
            else if (columnName.ToUpper().Equals("ID"))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.BadRequest;
            }
            else
            {
                db.Tables[tableName].Columns[columnName].Drop();
                connection.Close();
                return;
            }
        }

        public void DeleteRowInTable(string tableName, string rowID)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            Database db = GetDatabase(connection);
            if (!db.Tables.Contains(tableName))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                connection.Close();
                return;
            }
            else
            {
                SqlCommand cmd = new SqlCommand( "DELETE From " + tableName + " WHERE [id;text/plain] = @Id",connection);
                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = int.Parse(rowID);
                cmd.ExecuteNonQuery();
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NoContent;
                connection.Close();
                return;
            }
        }

        public void DeleteTable(string tableName)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            Database db = GetDatabase(connection);
            if (!db.Tables.Contains(tableName))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                connection.Close();
                return;
            }
            else
            {
                db.Tables[tableName].Drop();
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NoContent;
                connection.Close();
                return;
            }
        }
        /// <summary>
        /// Add data to column
        /// </summary>
        /// <param name="data">The data stream</param>
        /// <param name="tableName">The table name</param>
        /// <param name="rowID">The id of the row</param>
        /// <param name="columnName">The name of the column</param>
        public void InsertDataToColumn(Stream data, string tableName, string rowID, string columnName)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            Database db = GetDatabase(connection);
            if (!db.Tables.Contains(tableName) )
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                return;
            }
            string realColName = getColumnName(columnName,db.Tables[tableName]);
            if(realColName == null)
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                return;
            }
            else
            {
                byte[] byteData = GetBytesFromStream(data);
                SqlCommand command = new SqlCommand("SELECT [" + realColName +"] FROM "+ tableName +" WHERE [id;text/plain]=@Id", connection);
                command.Parameters.Add("@Id", SqlDbType.Int).Value = int.Parse(rowID);
                SqlCommand updateCommand = new SqlCommand("Update " +tableName+" SET ["+realColName+"] = @data WHERE [id;text/plain]=@Id", connection);
                updateCommand.Parameters.Add("@Id", SqlDbType.Int).Value = int.Parse(rowID);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                
                DataSet sqlData = new DataSet();
                SqlDataType type = db.Tables[tableName].Columns[realColName].DataType.SqlDataType;
                updateCommand.Parameters.Add("@data", getSQLDbTypeFromSqlDataType(type)).Value = ConvertByteToObject(byteData, type);
               
                dataAdapter.UpdateCommand = updateCommand;
                updateCommand.ExecuteNonQuery();
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.Created;
                return;
                /*dataAdapter.Fill(sqlData,tableName);
                sqlData.Tables[tableName].Rows[0][realColName] = ConvertByteToObject(byteData,type);
                dataAdapter.Update(sqlData, tableName);
                connection.Close();*/

            }
        }

        /// <summary>
        /// Add a new row to a table
        /// </summary>
        /// <param name="tableName">The name of the table</param>
        public void AddNewRow(string tableName)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            Database db = GetDatabase(connection);
            if (!db.Tables.Contains(tableName))
            {
                WebOperationContext.Current.OutgoingResponse.StatusCode = HttpStatusCode.NotFound;
                return;
            }
            else
            {
                SqlDataAdapter adapter = new SqlDataAdapter("Select * From "+tableName + " order by [id;text/plain]", connection);
                DataSet database = new DataSet();
                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
                commandBuilder.GetUpdateCommand();
                commandBuilder.GetInsertCommand();
                adapter.Fill(database, 0, 1, tableName);
                
                DataRow row = database.Tables[tableName].NewRow();
                for (int i = 1; i < database.Tables[tableName].Columns.Count; i++)
                {
                    row[i] = Convert.DBNull;
                }
                database.Tables[tableName].Rows.Add(row);
                adapter.Update(database,tableName);
            }
        }

        
    }
}
